﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Data;
using MySql.Data;

//van datatable naar datagridview

namespace BDM
{
	public class Database
	{
		//Fields
		private MySqlConnection _con;

		//Constructor
		public Database()
		{
			string parameters = "SERVER=localhost;DATABASE=kmheffing;UID=root;PASSWORD=;";
			this._con = new MySqlConnection(parameters);
		}

		//Methods
		//Private
		
		//public 
		public bool isConnected()
		{
			try
			{
				this._con.Open();
			}
			catch
			{
				return false;
			}
			return true;
		}

		public DataTable GetQuery(string sql)
		{
			DataSet dataSet = new System.Data.DataSet("tab1");
			DataTable dt = null;

			_con.Open();
			
			MySqlDataAdapter adapter = new MySqlDataAdapter();
			MySqlCommand command = _con.CreateCommand();
			command.CommandText = sql;
			adapter.SelectCommand = command;
			adapter.Fill(dataSet, "tab1");
			dt = dataSet.Tables["tab1"];
			
			_con.Close();

			return dt;
		}

		public void PutQuery(string sql)
		{
			if (_con.State != ConnectionState.Open)
			{
				_con.Open();
			}
			MySqlCommand command = _con.CreateCommand();
			command.CommandText = sql;
			command.ExecuteNonQuery();

			_con.Close();
		}

		public object GetInsertId()
		{
			DataTable dt = GetQuery("SELECT LAST_INSERT_ID() AS `id`;");
			
			if (dt.Rows.Count == 1)
			{
				return dt.Rows[0].ItemArray[0];
			}
			else
			{
				return null;
			}
		}

		public object GetNextAutoIncrementId(string table)
		{
			DataTable dt = GetQuery("SHOW TABLE STATUS LIKE '"+table+"'");

			if (dt.Rows.Count == 1)
			{
				return dt.Rows[0]["Auto_increment"];
			}
			else
			{
				return null;
			}
		}

		public DataTable storedProcedureCall(string procNaam)
		{
			DataTable dt = null;

			try
			{
				DataSet dataSet = new System.Data.DataSet("tab1");
				MySqlCommand cmd = new MySqlCommand(procNaam, this._con);
				cmd.CommandType = CommandType.StoredProcedure;
				MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
				adapter.Fill(dataSet, "tab1");
				dt = dataSet.Tables["tab1"];
			}
			catch (MySqlException e)
			{
				MessageBox.Show(e.Message);
			}
			finally
			{
				this._con.Close();
			}

			return dt;
		}

        public Boolean IsReachable()
        {
            try
            {
                _con.Open();
            }
            catch (Exception)
            {
                return false;
            }

            _con.Close();

            return true;
        }
	}
}